package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.dao.FormulaDAO;
import edu.cmu.cs.webapp.ioms.models.Client;
import edu.cmu.cs.webapp.ioms.models.FormularRecord;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.Ingredient;
import edu.cmu.cs.webapp.ioms.models.OrderStatus;
import edu.cmu.cs.webapp.ioms.models.Product;
import edu.cmu.cs.webapp.ioms.models.PurchaseOrder;

public class FormulaDAOImpl extends DAO implements FormulaDAO{

	public FormulaDAOImpl() {
		// TODO Auto-generated constructor stub
		super();
	}
	
	public List<FormularRecord> getAllForm() throws DataAccessException{
		Connection con = null;
		FormularRecord form = null;
		Product prod = null;
		Ingredient ing =null;
        List<FormularRecord> formlst = new ArrayList<FormularRecord>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from formula";
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		form = IOMSPackage.eINSTANCE.getIOMSFactory().createFormularRecord();
        		prod = IOMSPackage.eINSTANCE.getIOMSFactory().createProduct();
        		ing = IOMSPackage.eINSTANCE.getIOMSFactory().createIngredient();
        		
        		form.setForm_id(rs.getInt("form_id"));
        		prod.setProd_id(rs.getInt("prod_id"));
        		form.setProduct(prod);
        		
        		ing.setIng_id(rs.getInt("ing_id"));
        		form.setIngredient(ing);
        		
        		form.setIng_std_quantity(rs.getDouble("ing_std_quantity"));

        		formlst.add(form);
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return formlst;
	}

	@Override
	public List<FormularRecord> getFormProd(Product prod)
			throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		FormularRecord form = null;
		Product p = null;
		Ingredient ing =null;
        List<FormularRecord> formlst = new ArrayList<FormularRecord>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from formula where prod_id=" + prod.getProd_id();
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		form = IOMSPackage.eINSTANCE.getIOMSFactory().createFormularRecord();
        		p = IOMSPackage.eINSTANCE.getIOMSFactory().createProduct();
        		ing = IOMSPackage.eINSTANCE.getIOMSFactory().createIngredient();
        		
        		form.setForm_id(rs.getInt("form_id"));
        		p.setProd_id(rs.getInt("prod_id"));
        		form.setProduct(p);
        		
        		ing.setIng_id(rs.getInt("ing_id"));
        		form.setIngredient(ing);
        		
        		form.setIng_std_quantity(rs.getDouble("ing_std_quantity"));

        		formlst.add(form);
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return formlst;
	}

	@Override
	public void addFormula(FormularRecord form) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();

			PreparedStatement pstmt = con
					.prepareStatement("insert into formula(prod_id, ing_id, ing_std_quantity) "
							+ "values (? ,?, ?)");

			pstmt.setInt(1, form.getProduct().getProd_id());
			pstmt.setInt(2, form.getIngredient().getIng_id());
			pstmt.setDouble(3, form.getIng_std_quantity());

			pstmt.executeUpdate();
			System.out.println("insert: " + form.toString());

			pstmt.close();
			con.close();
			
			log("Create formula [" + form.toString() + "]", "Add");

		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
	}

	@Override
	public boolean deleteFormula(FormularRecord form) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement pstmt = con
					.prepareStatement("delete from formula where form_id ="
							+ form.getForm_id());
			pstmt.executeUpdate();
			System.out.println("delete: " + form.toString());

			pstmt.close();
			con.close();
			
			log("Delete formula [" + form.toString() + "]", "Delete");

		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public boolean updateFormula(FormularRecord form) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			
			String str="update formula set ";
			if( form.getProduct()!= null )
				str += "prod_id=" + form.getProduct().getProd_id() + ",";
			if( form.getIngredient() != null)
				str += "ing_id=" + form.getIngredient().getIng_id() +",";
			if( form.getIng_std_quantity() != 0)
				str += "ing_std_quantity=" + form.getIng_std_quantity() +",";


			str = str.substring(0, str.lastIndexOf(","));
			str += " where form_id=" + form.getForm_id();	
			System.out.println(str);
            PreparedStatement pstmt = con.prepareStatement(str);
            pstmt.executeUpdate();
            System.out.println("update: " + form.toString());
            		
			pstmt.close();
			con.close();
			
			log("Update formula [" + form.toString() + "]", "Update");

		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
		return true;
	}
}
